const Database = require('better-sqlite3');
const db = new Database('data/delivery-management.db');

console.log('Checking database structure...');

// Get all tables
const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
console.log('Total tables:', tables.length);

// Filter instance tables
const instanceTables = tables.filter(t => t.name.startsWith('device_instances_'));
console.log('Instance tables:', instanceTables.length);

// Filter parameter tables
const paramTables = tables.filter(t => t.name.startsWith('table_c'));
console.log('Parameter tables:', paramTables.length);

// Check for any data in instance tables
let totalInstanceRecords = 0;
console.log('\nChecking instance tables for data...');
for (const table of instanceTables.slice(0, 10)) {
  try {
    const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
    totalInstanceRecords += count.count;
    if (count.count > 0) {
      console.log(`${table.name}: ${count.count} records`);
      // Show sample record
      const sample = db.prepare(`SELECT * FROM [${table.name}] LIMIT 1`).get();
      console.log('  Sample:', {
        id: sample.id,
        device_type_code: sample.device_type_code,
        object_number: sample.object_number,
        keys: Object.keys(sample).length
      });
    }
  } catch (error) {
    console.log(`Error with ${table.name}: ${error.message}`);
  }
}

console.log('Total instance records checked:', totalInstanceRecords);

// Check parameter tables
let totalParamRecords = 0;
console.log('\nChecking parameter tables for data...');
for (const table of paramTables.slice(0, 10)) {
  try {
    const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
    totalParamRecords += count.count;
    if (count.count > 0) {
      console.log(`${table.name}: ${count.count} records`);
    }
  } catch (error) {
    console.log(`Error with ${table.name}: ${error.message}`);
  }
}

console.log('Total parameter records checked:', totalParamRecords);

// Check device_type_mappings table
console.log('\nChecking device_type_mappings...');
try {
  const mappings = db.prepare("SELECT COUNT(*) as count FROM device_type_mappings").get();
  console.log('Device type mappings:', mappings.count);
  
  if (mappings.count > 0) {
    const sample = db.prepare("SELECT * FROM device_type_mappings LIMIT 3").all();
    console.log('Sample mappings:');
    sample.forEach(mapping => {
      console.log(`  ${mapping.specialty}: ${mapping.classification_name}`);
      console.log(`    naming: ${mapping.naming_table}, position: ${mapping.position_table}, params: ${mapping.base_params_table}`);
    });
  }
} catch (error) {
  console.log('Error checking device_type_mappings:', error.message);
}

db.close();